How to: Perform calculations on data in a query.
Solution:
In Query Design View, use the 'Totals' row of the query to create a predefined calculation on all records or type an expression in the 'Field' cell to create a custom calculation.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Select the desired query in the list box. (The selected query is highlighted.)
4) Click 'Design'. (The query appears in Design View.)
5) To perform a calculation based on a group of records or all records, do the following:
a) Select the 'View' menu and select 'Totals'. (The Totals row appears in the query grid.)
Totals row
b) Click in the 'Totals' cell of the desired field. (A down arrow appears.)
c) Click down arrow. (A drop-down list appears.)
d) Select one type of total from the 'Totals' drop-down list.
NOTE: Predefined calculations include sun, average, count, minimum, maximum, standard deviation, and variance.
e) Repeat steps 5)b) through 5)d) for each additional calculation of fields.
f) Select the 'Query' menu and select 'Run' to view the results of the calculation.
6) To perform a custom calculation for each record, do the following:
a) Click in a blank cell of the 'Field' row. (The cursor blinks.)
b) Type the desired calculation using the following syntax
<Expression>:[fieldname] operator [fieldname]
EXAMPLE: Total:[Unit Price] * [Quantity]
(where <Total> is the expression name to store the value, [Unit Price] is a field name in the underlying table or query, '*' is to multiply, and [Quantity] is a field name in the underlying table or query).
c) Repeat steps 6)a) and 6)b) for each additional custom calculation.
d) Select the 'Query' menu and select 'Run' to view the results of the query.
7) Select the 'File' menu and select 'Save' to save changes to the query.
